package com.huatai.bi.handler;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.huatai.bi.constant.SystemConstant;
import com.huatai.bi.entity.DataSetColumnEntity;
import com.huatai.bi.entity.DatasourceEntity;
import com.huatai.bi.entity.TableColumnEntity;
import com.huatai.bi.entity.TableEntity;
import com.huatai.bi.handler.datasource.MysqlDatasource;
import com.huatai.bi.handler.datasource.OracleDatasource;
import com.huatai.bi.handler.datasource.PostgreDatasource;
import com.huatai.common.error.exception.ServiceException;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Log4j2
public class DatasourceHandler {
    private Connection conn=null;//数据库连接
    public String type;
    public List<TableEntity> tableList= new ArrayList<>(); //表结构
    public List<TableColumnEntity> columnList= new ArrayList<>(); //表字段
    public List<String> schemaList = new ArrayList<>();
    public String version; //数据库版本
    public JSONArray jsonArray = new JSONArray(); //SQL查询结果
    public String sql; //执行的SQL
    public int pageStartIndex = 0;
    public int pageSize = 0;
    public int count = 0;
    private JSONObject param = null;//参数

    List<DataSetColumnEntity> dataSetColumnEntityList = new ArrayList<>();

    //获取连接
    public DatasourceHandler(DatasourceEntity entity) {
        try {
            String jdbc = jdbcUrl(entity);
            conn = DatasourceJdbcPool.get(entity,jdbc);
        }catch (Exception e){
            throw new ServiceException("数据库连接异常，异常信息："+e.getMessage());
        }
    }

    public static DatasourceHandler datasourceFactory(DatasourceEntity entity){
        switch (entity.getType()){
            case SystemConstant.DATASOURCE_TYPE_MYSQL:return new MysqlDatasource(entity);
            case SystemConstant.DATASOURCE_TYPE_ORACLE:return new OracleDatasource(entity);
            case SystemConstant.DATASOURCE_TYPE_GAUSSDB:
            case SystemConstant.DATASOURCE_TYPE_PG:return new PostgreDatasource(entity);
            default:return null;
        }
    }

    //执行sql
    private void executeQuery(){
        PreparedStatement statement=null;
        ResultSet re = null;
        try {
            List<String> columnNameList  = new ArrayList<>();
            int index = 0;
            //参数处理,参数处理成占位符
            while ((index = sql.indexOf("${"))>0){
                String str = sql.substring(index,sql.indexOf("}")+1);
                String columnName = str.substring(2,str.length()-1);
                columnNameList.add(columnName.trim());
                sql = sql.replaceAll("\\$\\{"+columnName+"}","?");
            }
            log.info(sql);
            statement = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //设置参数
            statementSetValue(statement,columnNameList);
            re = statement.executeQuery();
            //结果集处理
            result(re);
        }catch (Exception e){
            log.error(e.getMessage());
            closeConn();
            throw new ServiceException("获取数据异常,异常信息:"+e.getMessage());
        }finally {
            try {
                if(null != re){
                    re.close();
                }
                if(null != statement){
                    statement.close();
                }
            }catch (Exception e){}
        }
    }



    //jdbc连接信息处理，主要处理pg和mysql字符串和pg中schema
    protected String jdbcUrl(DatasourceEntity entity){return entity.getJdbcUrl();}

    //SQL处理
    protected void sqlInit(){}

    //处理SQL执行结果
    protected void result(ResultSet re) throws SQLException {
        switch (type){
            case SystemConstant.JDBC_SQL_TYPE_TABLE:
                while (re.next()){
                    TableEntity table = new TableEntity();
                    table.setTableCatalog(re.getString(1));
                    table.setTableSchema(re.getString(2));
                    table.setTableName(re.getString(3));
                    table.setTableComment(re.getString(4));
                    //table.setTableCreateTime(LocalDateTime.ofInstant(re.getTimestamp(5).toInstant(), ZoneId.systemDefault()));
                    tableList.add(table);
                }
                return;
            case SystemConstant.JDBC_SQL_TYPE_COLUMN:
                while (re.next()){
                    TableColumnEntity column = new TableColumnEntity();
                    column.setTableName(re.getString(1));
                    column.setColumnName(re.getString(2));
                    column.setColumnComment(re.getString(3));
                    column.setColumnType(re.getString(4));
                    column.setColumnPosition(re.getInt(5));
                    column.setDefaultValue(re.getString(6));
                    column.setIsNullable(re.getString(7));
                    columnList.add(column);
                }
                return;
            case SystemConstant.JDBC_SQL_TYPE_VERSION:
                while (re.next()){
                    version = re.getString(1);
                }
                return;
            case SystemConstant.JDBC_SQL_TYPE_SCHEMA:
                while (re.next()){
                    schemaList.add(re.getString(1));
                }
                return;
            case SystemConstant.JDBC_SQL_TYPE_SQL:
            case SystemConstant.JDBC_SQL_TYPE_SQL_PAGE:
                ResultSetMetaData meta = re.getMetaData();
                List<String> lables = new ArrayList<>();
                for (int i = 1; i <=  meta.getColumnCount(); i++) {
                    lables.add(meta.getColumnLabel(i));
                }
                while (re.next()){
                    JSONObject data = new JSONObject();
                    for (int i = 0; i < lables.size(); i++) {
                        data.put(lables.get(i),re.getObject(i+1));
                    }
                    jsonArray.add(data);
                }
                return;
            case SystemConstant.JDBC_SQL_TYPE_SQL_PAGE_COUNT:
                while (re.next()){
                    count = re.getInt(1);
                }
                return;
            case SystemConstant.JDBC_SQL_TYPE_SQL_COLUMN:
                ResultSetMetaData metaData = re.getMetaData();
                int num = metaData.getColumnCount();
                for (int i = 1; i < num+1; i++) {
                    DataSetColumnEntity entity = new DataSetColumnEntity();
                    entity.setColumnName(metaData.getColumnName(i));
                    String columnType = SystemConstant.DATA_SET_COLUMN_TYPE_MAP.get(metaData.getColumnClassName(i));
                    if(StringUtils.isBlank(columnType)){
                        columnType = "string";
                    }
                    entity.setColumnComment(entity.getColumnName());
                    entity.setColumnType(columnType);
                    entity.setColumnPosition(i);
                    dataSetColumnEntityList.add(entity);
                }
                return;
            default:return;
        }
    }

    //关闭连接
    public void closeConn(){
        try {
            if(null != conn && !conn.isClosed()){
                conn.close();
            }
        }catch (Exception e){}
    }

    //获取表结构
    public List<TableEntity> getTables(){
		tableList.clear();
        type = SystemConstant.JDBC_SQL_TYPE_TABLE;
        sqlInit();
        executeQuery();
        return tableList;
    }

    //获取表字段
    public List<TableColumnEntity> getTableColumns(){
		columnList.clear();
        type = SystemConstant.JDBC_SQL_TYPE_COLUMN;
        sqlInit();
        executeQuery();
        return columnList;
    }

    //获取数据库版本号
    public String getVersion(){
        type = SystemConstant.JDBC_SQL_TYPE_VERSION;
        sqlInit();
        executeQuery();
        return version;
    }

    //获取schema
    public List<String> getSchema(){
		schemaList.clear();
        type = SystemConstant.JDBC_SQL_TYPE_SCHEMA;
        sqlInit();
        executeQuery();
        return schemaList;
    }

    //根据SQL查询数据
    public JSONArray getDataBySql(String sql){
		jsonArray.clear();
        type = SystemConstant.JDBC_SQL_TYPE_SQL;
        this.sql = sql;
        executeQuery();
        return jsonArray;
    }

    //根据SQL查询数据
    public JSONArray getDataBySql(String sql,JSONObject param){
		jsonArray.clear();
        type = SystemConstant.JDBC_SQL_TYPE_SQL;
        this.sql = sql;
        this.param = param;
        executeQuery();
        return jsonArray;
    }

    //根据SQL查询分页数据
    public IPage getPageDataBySql(String sql,Integer pageIndex,Integer pageSize,JSONObject param){
		jsonArray.clear();
        type = SystemConstant.JDBC_SQL_TYPE_SQL_PAGE;
        this.sql = sql;
        this.param = param;
        if(ObjectUtils.anyNull(pageIndex,pageSize)){
            log.error("pageIndex:{},pageSize:{}",pageIndex,pageSize);
            throw  new ServiceException("分页参数异常");
        }

        pageStartIndex = (pageIndex-1)*pageSize;
        this.pageSize =pageSize;

        sqlInit();
        executeQuery();
        type = SystemConstant.JDBC_SQL_TYPE_SQL_PAGE_COUNT;
        this.sql = sql;
        sqlInit();
        executeQuery();

        IPage page = new Page();
        page.setRecords(jsonArray);
        page.setTotal(count);
        page.setSize(pageSize);
        return page;
    }

    public List<DataSetColumnEntity> getSqlColumn(String sql,JSONObject param){
		dataSetColumnEntityList.clear();
        this.type = SystemConstant.JDBC_SQL_TYPE_SQL_COLUMN;
        this.param = param;
        this.sql = sql;
        executeQuery();
        return dataSetColumnEntityList;
    }

    //根据表名生成查询sql
    public static String getSqlBytableName(String tableName){
        return "select * from "+tableName;
    }

    private void statementSetValue(PreparedStatement statement,List<String> columnNameList) throws SQLException {

        if(columnNameList.size() > 0){
            for (int i = 0; i < columnNameList.size(); i++) {
                Object value = param.get(columnNameList.get(i));
                if(null == value){
                    throw new ServiceException("参数异常，参数"+columnNameList.get(i)+"不存在");
                }
                if(value instanceof String){
                    statement.setString(i+1,(String)value);
                }else if(value instanceof Integer){
                    statement.setInt(i+1,(Integer) value);
                }else if(value instanceof Long){
                    statement.setLong(i+1,(Long) value);
                }else if(value instanceof Double){
                    statement.setDouble(i+1,(Double) value);
                }else if(value instanceof Float){
                    statement.setFloat(i+1,(Float) value);
                }else if(value instanceof BigDecimal){
                    statement.setBigDecimal(i+1,(BigDecimal) value);
                }else if(value instanceof Date){
                    statement.setTimestamp(i+1,new Timestamp(((Date)value).getTime()));
                }else if(value instanceof LocalDateTime){
                    statement.setTimestamp(i+1,new Timestamp(((LocalDateTime)value).toInstant(ZoneOffset.of("+8")).toEpochMilli()));
                }else{
                    statement.setString(i+1,(String) value);
                }


            }
        }
    }
}
